/**
 * 
 */
package com.cipe.cmrs.service;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import com.cipe.cmrs.bean.CipeprojectBean;
import com.cipe.cmrs.exception.CipeprojectException;
import com.cipe.cmrs.exception.QueryException;
import com.cipe.cmrs.helper.PaymentHelper;
import com.cipe.cmrs.model.Audits;
import com.cipe.cmrs.model.Cipeproject;
import com.cipe.cmrs.model.Cipeprojectmodification;
import com.cipe.cmrs.model.Cipeprojectstatus;
import com.cipe.cmrs.model.Cipeprojecttype;
import com.cipe.cmrs.model.Country;
import com.cipe.cmrs.model.Donoraward;
import com.cipe.cmrs.model.Keyword;
import com.cipe.cmrs.model.Modificationtype;
import com.cipe.cmrs.model.Organizationtype;
import com.cipe.cmrs.model.Region;
import com.cipe.cmrs.model.User;
import com.cipe.cmrs.model.view.ProjectPaymentView;
import com.cipe.cmrs.util.Constants;
import com.cipe.cmrs.util.DateUtil;
import com.cipe.cmrs.util.DateUtil2;
import com.cipe.cmrs.util.NumberUtil;
import com.cipe.cmrs.util.UserUtil;
import com.cipe.cmrs.util.Util;

/**
 * @author Incepio
 */
@Service
@Transactional
public class CipeprojectService extends BaseService<Cipeproject> {

	@Autowired
	private CipeprojecttypeService cipeprojecttypeService;
	@Autowired
	private CipeprojectstatusService cipeprojectstatusService;
	@Autowired
	private AuditsService auditsService;
	@Autowired
	private ModificationTypeService modificationTypeService;
	@Autowired
	private KeywordService keywordService;
	@Autowired
    private PaymentHelper paymentHelper;
	/**
	 * @param id
	 */
	private Cipeproject findByCostCenterNumber(Integer id) {
		Cipeproject cipeproject = null;
		try {
			String query = "from Cipeproject where " + Util.createFirstQueryClause("costCenterNumber", id);
			cipeproject = this.dao.getSingleResult(query);
		} catch (Exception exp) {
			logger.error(exp.getMessage(), exp);
		}
		return cipeproject;
	}

	@Transactional(propagation = Propagation.SUPPORTS, readOnly = false)
	public Long countByProjectID(String id) {
		String query = "select count(o) from Cipeproject o where " + Util.createFirstQueryClause("projectID", id);
		List results = this.dao.getResultList(query);

		return (results != null ? (Long) results.get(0) : 0L);
	}

	/**
	 * This method will call database procedure with native query and get the
	 * result\
	 * 
	 * @author Krushna
	 * @param procedureName
	 * @return
	 */
	public List<Cipeproject> findCipeproject(String procedureName) {
		return this.dao.getResultListWithNativeQuery(procedureName, Util.createOrderByDesc("projectid"), Cipeproject.class);
	}

	/**
	 * Return the list of project with paymentrequestid
	 * 
	 * @param id
	 * @return
	 */
	public List<Cipeproject> findCipeproject(int id, String procedureName) {
		return this.dao.getResultListWithNativeQuery(procedureName, Util.createOrderByDesc("projectid"), Cipeproject.class, id);
	}

	public List<Cipeproject> findCipeprojectByDonor(Donoraward donoraward) {
		List<Cipeproject> cipeprojectList = new ArrayList<Cipeproject>();
		String query = "from Cipeproject where";		
		try {
			if (donoraward != null){
				query = query + Util.createlikeFirstQueryClause("donorawardid", donoraward.getId().toString());		
			}
			//logger.debug(query);
			cipeprojectList = this.dao.getResultList(query);
		} catch (Exception exp) {
			logger.error(exp.getMessage(), exp);
		}
		return cipeprojectList;
	}
	
	public List<Cipeproject> findCipeproject(Cipeproject cipeproject) {
		/*
		 * Object[] params = { cipeproject.getProjectID(),
		 * cipeproject.getAcronym(), cipeproject.getPartner() != null ?
		 * cipeproject.getPartner() : null, cipeproject.getProjectTitle(),
		 * cipeproject.getCipeprojecttype() != null ?
		 * cipeproject.getCipeprojecttype() : null,
		 * cipeproject.getProgramOfficer() != null ?
		 * cipeproject.getProgramOfficer() : null,
		 * cipeproject.getProgramAssistant() != null ?
		 * cipeproject.getProgramAssistant() : null, cipeproject.getRegion() !=
		 * null ? cipeproject.getRegion() : null, cipeproject.getCountry() !=
		 * null ? cipeproject.getCountry() : null,
		 * cipeproject.getCipeprojectstatus() != null ?
		 * cipeproject.getCipeprojectstatus() : null,
		 * cipeproject.getFiscalYear(),
		 * cipeproject.getInitialProjectStartDate(),
		 * cipeproject.getFinalProjectStartDate(),
		 * cipeproject.getInitialProjectEndDate(),
		 * cipeproject.getFinalProjectEndDate(),
		 * cipeproject.getInitialManagementFunds(),
		 * cipeproject.getFinalManagementFunds() };
		 */
		List<Cipeproject> cipeprojectList = new ArrayList<Cipeproject>();
		try {
			 Cipeprojecttype typePartnerGrant = this.cipeprojecttypeService.findByDisplayName(Constants.PartnerGrant);
			 Cipeprojecttype typeBalanceSheet = this.cipeprojecttypeService.findByDisplayName(Constants.BalanceSheet);
			String query = "from Cipeproject where";
			query = query + Util.createlikeFirstQueryClause("projectID", cipeproject.getProjectID(), true);
			if (cipeproject.getAcronym() != null && !"".equals(cipeproject.getAcronym()))
				query = query + Util.createlikeQueryClause("acronym", cipeproject.getAcronym());

			if (cipeproject.getPartner() != null)
				query = query + Util.createQueryClause(cipeproject.getPartner());

			if (cipeproject.getProjectTitle() != null && !"".equals(cipeproject.getProjectTitle()))
				query = query + Util.createlikeQueryClause("projectTitle", cipeproject.getProjectTitle());

			if (cipeproject.getCipeprojecttype() != null
					&& cipeproject.getCipeprojecttype().getId() != null) {
				query = query + Util.createQueryClause(cipeproject.getCipeprojecttype());
			} else {
				if (cipeproject.getProjectID() == null || "".equals(cipeproject.getProjectID())) {
					query = query + " and cipeprojecttype.id!="+typePartnerGrant.getId();
					query = query + " and cipeprojecttype.id!="+typeBalanceSheet.getId();
				}
			}

			if (cipeproject.getProgramOfficer() != null)
				query = query + Util.createQueryClause(cipeproject.getProgramOfficer(), "programOfficer");

			if (cipeproject.getProgramAssistant() != null)
				query = query + Util.createQueryClause(cipeproject.getProgramAssistant(), "programAssistant");

			if (cipeproject.getRegion() != null)
				query = query + Util.createQueryClause(cipeproject.getRegion());
			if (cipeproject.getCountry() != null)
				query = query + Util.createQueryClause(cipeproject.getCountry());

			if (cipeproject.getCipeprojectstatus() != null)
				query = query + Util.createQueryClause(cipeproject.getCipeprojectstatus());

			if (cipeproject.getFiscalYear() != null && !"".equals(cipeproject.getFiscalYear()))
				//query = query + Util.createRangeQueryClause("fiscalYear", cipeproject.getFiscalYear(),cipeproject.getFiscalYear2());
				if (cipeproject.getFiscalYear2() != null && !"".equals(cipeproject.getFiscalYear2()))
					query = query + Util.createRangeQueryClause("fiscalYear", cipeproject.getFiscalYear(),cipeproject.getFiscalYear2());
				else
					query = query + Util.createGTEQQueryClause("fiscalYear", cipeproject.getFiscalYear());
			else
				if (cipeproject.getFiscalYear2() != null && !"".equals(cipeproject.getFiscalYear2()))
					query = query + Util.createLTEQQueryClause("fiscalYear", cipeproject.getFiscalYear2());

			if (cipeproject.getInitialProjectStartDate() != null)
				//query = query + Util.createRangeQueryClause("projectStartDate", cipeproject.getInitialProjectStartDate(), cipeproject.getFinalProjectStartDate());
				if (cipeproject.getFinalProjectStartDate() != null)
					query = query + Util.createRangeQueryClause("projectStartDate", cipeproject.getInitialProjectStartDate(), cipeproject.getFinalProjectStartDate());
				else
					query = query + Util.createGTEQQueryClause("projectStartDate", cipeproject.getInitialProjectStartDate());
			else
				if (cipeproject.getFinalProjectStartDate() != null)
					query = query + Util.createLTEQQueryClause("projectStartDate", cipeproject.getFinalProjectStartDate());

			if (cipeproject.getInitialProjectEndDate() != null)
				//query = query + Util.createRangeQueryClause("projectEndDate", cipeproject.getInitialProjectEndDate(), cipeproject.getFinalProjectEndDate());
				if (cipeproject.getFinalProjectEndDate() != null)
					query = query + Util.createRangeQueryClause("projectEndDate", cipeproject.getInitialProjectEndDate(), cipeproject.getFinalProjectEndDate());
				else
					query = query + Util.createGTEQQueryClause("projectEndDate", cipeproject.getInitialProjectEndDate());
			else
				if (cipeproject.getFinalProjectEndDate() != null)
					query = query + Util.createLTEQQueryClause("projectEndDate", cipeproject.getFinalProjectEndDate());

			if (cipeproject.getInitialManagementFunds() != null)
				query = query + Util.createRangeQueryClause("managementFunds", cipeproject.getInitialManagementFunds(), cipeproject.getFinalManagementFunds());
			//query = query + Util.createOrderByDesc("projectenddate");
			if (cipeproject.getFiscalYear() != null && !"".equals(cipeproject.getFiscalYear())
					|| cipeproject.getFiscalYear2() != null && !"".equals(cipeproject.getFiscalYear2()))
				query = query + Util.createOrderByDesc("projectID");
			else
				query = query + Util.createOrderByDesc("projectenddate");
			//logger.debug(query);
			cipeprojectList = this.dao.getResultList(query);
			// cipeprojectList = dao.getNamedQueryResultList("findCipeproject",
			// params);
		} catch (Exception exp) {
			logger.error(exp.getMessage(), exp);
		}
		return cipeprojectList;
	}

	public List<Cipeproject> findKnowledgeManagement(Cipeproject cipeproject) {
		List<Cipeproject> cipeprojectList = new ArrayList<Cipeproject>();
		try {
			 Cipeprojecttype typePartnerGrant = this.cipeprojecttypeService.findByDisplayName(Constants.PartnerGrant);
			 Cipeprojecttype typeBalanceSheet = this.cipeprojecttypeService.findByDisplayName(Constants.BalanceSheet);
			StringBuffer buff = new StringBuffer();

		    SimpleDateFormat defaultDateFormatter = new SimpleDateFormat(Constants.DefaultDateFormat);
		    defaultDateFormatter.setLenient(true);
		    Pattern monthDayStringPattern = Pattern.compile(Constants.MonthDayStringFormat);
		
			String query = "select distinct p from Cipeproject as p where";
			if (cipeproject.getKeywordId1() != null || cipeproject.getKeywordId2() != null || cipeproject.getKeywordId3() != null) {
				query = " select distinct p from Cipeproject as p inner JOIN p.keywords as keyword inner JOIN p.keywords as keyword2 inner JOIN p.keywords as keyword3 where";
			}
			query = query + Util.createlikeFirstQueryClause("p.projectID", cipeproject.getProjectID(), true);
			if (cipeproject.getAcronym() != null && !"".equals(cipeproject.getAcronym()))
				query = query + Util.createlikeQueryClause("p.acronym", cipeproject.getAcronym());
		
			if (cipeproject.getPartner() != null)
				query = query + Util.createQueryClause("p.partner.id",cipeproject.getPartner().getId());

			if (cipeproject.getProjectTitle() != null && !"".equals(cipeproject.getProjectTitle()))
				query = query + Util.createQueryClause("p.projectTitle", cipeproject.getProjectTitle());
			if (cipeproject.getOrganizationtype() != null)
				query = query + Util.createQueryClause("p.organizationtype.id", cipeproject.getOrganizationtype().getId());
			query = query + " and p.cipeprojecttype.id!="+typePartnerGrant.getId();
			query = query + " and p.cipeprojecttype.id!="+typeBalanceSheet.getId();
			
			if (cipeproject.getProgramOfficer() != null)
				query = query + Util.createQueryClause(cipeproject.getProgramOfficer(), "p.programOfficer");
			
			if (cipeproject.getProgramAssistant() != null)
				query = query + Util.createQueryClause(cipeproject.getProgramAssistant(), "p.programAssistant");

			if (cipeproject.getRegion() != null)
				query = query + Util.createQueryClause(cipeproject.getRegion(), "p.region");

			if (cipeproject.getCountry() != null)
				query = query + Util.createQueryClause(cipeproject.getCountry(), "p.country");

			if (cipeproject.getThemebusassocdev() != null)
				query = query + Util.createGTEQQueryClause("p.themebusassocdev", cipeproject.getThemebusassocdev());

			if (cipeproject.getThemelegalregreform() != null)
				query = query + Util.createGTEQQueryClause("p.themelegalregreform", cipeproject.getThemelegalregreform());

			if (cipeproject.getThemewomenyouth() != null)
				query = query + Util.createGTEQQueryClause("p.themewomenyouth", cipeproject.getThemewomenyouth());

			if (cipeproject.getThemeaccesstoinfo() != null)
				query = query + Util.createGTEQQueryClause("p.themeaccesstoinfo", cipeproject.getThemeaccesstoinfo());

			if (cipeproject.getThemecorruption() != null)
				query = query + Util.createGTEQQueryClause("p.themecorruption", cipeproject.getThemecorruption());

			if (cipeproject.getThemecorporategovernance() != null)
				query = query + Util.createGTEQQueryClause("p.themecorporategovernance", cipeproject.getThemecorporategovernance());

			if (cipeproject.getThemedemocraticgovernance() != null)
				query = query + Util.createGTEQQueryClause("p.themedemocraticgovernance", cipeproject.getThemedemocraticgovernance());

			if (cipeproject.getCipeprojectstatus() != null)
				query = query + Util.createQueryClause(cipeproject.getCipeprojectstatus());

			if (cipeproject.getFiscalYear() != null && !"".equals(cipeproject.getFiscalYear()))
				//query = query + Util.createRangeQueryClause("p.fiscalYear", cipeproject.getFiscalYear(),cipeproject.getFiscalYear2());
				if (cipeproject.getFiscalYear2() != null && !"".equals(cipeproject.getFiscalYear2()))
					query = query + Util.createRangeQueryClause("p.fiscalYear", cipeproject.getFiscalYear(),cipeproject.getFiscalYear2());
				else
					query = query + Util.createGTEQQueryClause("p.fiscalYear", cipeproject.getFiscalYear());
			else
				if (cipeproject.getFiscalYear2() != null && !"".equals(cipeproject.getFiscalYear2()))
					query = query + Util.createLTEQQueryClause("p.fiscalYear", cipeproject.getFiscalYear2());

			/*if (cipeproject.getInitialProjectStartDate() != null)
				//query = query + Util.createRangeQueryClause("p.projectStartDate", cipeproject.getInitialProjectStartDate(), cipeproject.getFinalProjectStartDate());
				if (cipeproject.getFinalProjectStartDate() != null)
					query = query + Util.createRangeQueryClause("p.projectStartDate", cipeproject.getInitialProjectStartDate(), cipeproject.getFinalProjectStartDate());
				else
					query = query + Util.createGTEQQueryClause("p.projectStartDate", cipeproject.getInitialProjectStartDate());
			else
				if (cipeproject.getFinalProjectStartDate() != null)
					query = query + Util.createLTEQQueryClause("p.projectStartDate", cipeproject.getFinalProjectStartDate());

			if (cipeproject.getInitialProjectEndDate() != null)
				//query = query + Util.createRangeQueryClause("p.projectEndDate", cipeproject.getInitialProjectEndDate(), cipeproject.getFinalProjectEndDate());
				if (cipeproject.getFinalProjectEndDate() != null)
					query = query + Util.createRangeQueryClause("p.projectEndDate", cipeproject.getInitialProjectEndDate(), cipeproject.getFinalProjectEndDate());
				else
					query = query + Util.createGTEQQueryClause("p.projectEndDate", cipeproject.getInitialProjectEndDate());
			else
				if (cipeproject.getFinalProjectEndDate() != null)
					query = query + Util.createLTEQQueryClause("p.projectEndDate", cipeproject.getFinalProjectEndDate());*/

			if (StringUtils.isNotBlank(cipeproject.getInitialProjectStartDateString()))
				if (StringUtils.isNotBlank(cipeproject.getFinalProjectStartDateString())) {
					Timestamp initialProjectStartDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getInitialProjectStartDateString()));
					Timestamp finalProjectStartDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getFinalProjectStartDateString()));
					query = query + Util.createRangeQueryClause("p.projectStartDate", initialProjectStartDate, finalProjectStartDate);
				} else {
					if (monthDayStringPattern.matcher(cipeproject.getInitialProjectStartDateString()).matches()) {
						String[] initialProjectStartDateStrings = cipeproject.getInitialProjectStartDateString().split("/");
						query = query + Util.createMonthDayQueryClause("p.projectStartDate", initialProjectStartDateStrings[0], initialProjectStartDateStrings[1]);
					} else {
						Timestamp initialProjectStartDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getInitialProjectStartDateString()));
						query = query + Util.createGTEQQueryClause("p.projectStartDate", initialProjectStartDate);
					}
				}
			else
				if (StringUtils.isNotBlank(cipeproject.getFinalProjectStartDateString())) {
					Timestamp finalProjectStartDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getFinalProjectStartDateString()));
					query = query + Util.createLTEQQueryClause("p.projectStartDate", finalProjectStartDate);
				}

			if (StringUtils.isNotBlank(cipeproject.getInitialProjectEndDateString()))
				if (StringUtils.isNotBlank(cipeproject.getFinalProjectEndDateString())) {
					Timestamp initialProjectEndDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getInitialProjectEndDateString()));
					Timestamp finalProjectEndDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getFinalProjectEndDateString()));
					query = query + Util.createRangeQueryClause("p.projectEndDate", initialProjectEndDate, finalProjectEndDate);
				} else {
					if (monthDayStringPattern.matcher(cipeproject.getInitialProjectEndDateString()).matches()) {
						String[] initialProjectEndDateStrings = cipeproject.getInitialProjectEndDateString().split("/");
						query = query + Util.createMonthDayQueryClause("p.projectEndDate", initialProjectEndDateStrings[0], initialProjectEndDateStrings[1]);
					} else {
						Timestamp initialProjectEndDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getInitialProjectEndDateString()));
						query = query + Util.createGTEQQueryClause("p.projectEndDate", initialProjectEndDate);
					}
				}
			else
				if (StringUtils.isNotBlank(cipeproject.getFinalProjectEndDateString())) {
					Timestamp finalProjectEndDate = Util.getTimestamp(defaultDateFormatter.parse(cipeproject.getFinalProjectEndDateString()));
					query = query + Util.createLTEQQueryClause("p.projectEndDate", finalProjectEndDate);
				}

			if (cipeproject.getKeywordId1() != null || cipeproject.getKeywordId2()!=null || cipeproject.getKeywordId3()!=null) {
				buff.append(" and ( keyword.id = (");
				 boolean keyin=false;
				 boolean keyin2= false;
				 boolean condition1 = false;
				if(cipeproject.getKeywordId1() != null){
					buff.append(cipeproject.getKeywordId1());
					keyin = true;
				}
				String orq="";
				if(keyin && cipeproject.getKeywordId2() != null && "OR".equals(cipeproject.getCondition1()) ){					
					orq = orq + " OR keyword2.id=" + cipeproject.getKeywordId2();
					condition1= true;
				} else if( keyin && cipeproject.getKeywordId2() != null  ) {
					orq = orq + " and keyword2.id=" + cipeproject.getKeywordId2();
					keyin2 = true;					
				} else if(cipeproject.getKeywordId2()!=null) {
					buff.append(cipeproject.getKeywordId2() );
					keyin2 = true;
				} 
				if( (keyin || keyin2) && "OR".equals(cipeproject.getCondition2()) && cipeproject.getKeywordId3()!=null){					
					orq = orq + " OR keyword3.id=" + cipeproject.getKeywordId3();
				} else if((keyin || keyin2) && cipeproject.getKeywordId3()!=null) {
					orq = orq + " and keyword3.id=" + cipeproject.getKeywordId3();

					keyin = true;
				} else if(cipeproject.getKeywordId3()!=null){
					buff.append(cipeproject.getKeywordId3());
					keyin = true;
				}
				
				if(keyin || keyin2){
					buff.append(")");
					query = query + buff.toString();
				}
				query = query+orq;
				query+=")";
			}

			query = query + Util.createRangeQueryClause("p.managementFunds", cipeproject.getInitialManagementFunds(), cipeproject.getFinalManagementFunds());					
			String q = "from Cipeproject where id in("+query+")"/* + Util.createOrderByDesc(" projectStartDate")*/;
			if (cipeproject.getFiscalYear() != null && !"".equals(cipeproject.getFiscalYear())
					|| cipeproject.getFiscalYear2() != null && !"".equals(cipeproject.getFiscalYear2()))					
				q = q + Util.createOrderByDesc(" projectID");
			else
				q = q + Util.createOrderByDesc(" projectStartDate");
			logger.debug(q/*query*/);
			cipeprojectList = this.dao.getResultList(q);

		} catch (Exception exp) {
			logger.error(exp.getMessage(), exp);
		}
		return cipeprojectList;
	} 
	
    public List<Cipeproject> getRollUpLinkList(){
    	String query = "from Cipeproject where cipeprojecttype.displayName!='Partner' and cipeprojectstatus.displayName='Active'";
    	List<Cipeproject> cipeprojectList = new ArrayList<Cipeproject>();
    	try {
    		cipeprojectList = this.dao.getResultList(query);
    	}catch (Exception e) {
    		logger.error(e.getMessage(), e);
		}
    	return cipeprojectList;
    }
    
	public List<Cipeproject> findPartnergrant(Cipeproject cipeproject) {
		List<Cipeproject> cipeprojectList = new ArrayList<Cipeproject>();
		try {
			Cipeprojecttype type = this.cipeprojecttypeService.findByDisplayName(Constants.PartnerGrant);
			String query = "from Cipeproject where";
			query = query + Util.createlikeFirstQueryClause("projectID", cipeproject.getProjectID(), true);
			query = query + Util.createQueryClause(type)
					+ Util.createlikeQueryClause("acronym", cipeproject.getAcronym()) + Util.createQueryClause(cipeproject.getPartner())
					+ Util.createlikeQueryClause("projectTitle", cipeproject.getProjectTitle()) + Util.createQueryClause(cipeproject.getProgramOfficer(), "programOfficer")
					+ Util.createQueryClause(cipeproject.getProgramAssistant(), "programAssistant") + Util.createQueryClause(cipeproject.getRegion())
					+ Util.createQueryClause(cipeproject.getCountry()) + Util.createQueryClause(cipeproject.getCipeprojectstatus())
					+ Util.createQueryClause("fiscalYear", cipeproject.getFiscalYear());
			if (cipeproject.getInitialProjectStartDate() != null)
				//query = query + Util.createRangeQueryClause("projectStartDate", cipeproject.getInitialProjectStartDate(), cipeproject.getFinalProjectStartDate());
				if (cipeproject.getFinalProjectStartDate() != null)
					query = query + Util.createRangeQueryClause("projectStartDate", cipeproject.getInitialProjectStartDate(), cipeproject.getFinalProjectStartDate());
				else
					query = query + Util.createGTEQQueryClause("projectStartDate", cipeproject.getInitialProjectStartDate());
			else
				if (cipeproject.getFinalProjectStartDate() != null)
					query = query + Util.createLTEQQueryClause("projectStartDate", cipeproject.getFinalProjectStartDate());
			if (cipeproject.getInitialProjectEndDate() != null)
				//query = query + Util.createRangeQueryClause("projectEndDate", cipeproject.getInitialProjectEndDate(), cipeproject.getFinalProjectEndDate());
				if (cipeproject.getFinalProjectEndDate() != null)
					query = query + Util.createRangeQueryClause("projectEndDate", cipeproject.getInitialProjectEndDate(), cipeproject.getFinalProjectEndDate());
				else
					query = query + Util.createGTEQQueryClause("projectEndDate", cipeproject.getInitialProjectEndDate());
			else
				if (cipeproject.getFinalProjectEndDate() != null)
					query = query + Util.createLTEQQueryClause("projectEndDate", cipeproject.getFinalProjectEndDate());
			query = query + Util.createRangeQueryClause("managementFunds", cipeproject.getInitialManagementFunds(), cipeproject.getFinalManagementFunds())/*
					+ Util.createOrderByDesc("projectenddate")*/;
			if (cipeproject.getFiscalYear() != null && !"".equals(cipeproject.getFiscalYear()))
				query = query + Util.createOrderByDesc("projectID");
			else
				query = query + Util.createOrderByDesc("projectenddate");
			cipeprojectList = this.dao.getResultList(query);
		} catch (Exception exp) {
			logger.error(exp.getMessage(), exp);
		}
		return cipeprojectList;
	}

    @Transactional(readOnly = true)
    public List<ProjectPaymentView> getAllActiveCipeProjects(Integer excludePaymentRequest) {
        List<Object> parameters = new ArrayList<Object>();
        StringBuilder queryBuilder = new StringBuilder();
        queryBuilder.append("SELECT ");
        queryBuilder.append("obj.id, ");
        queryBuilder.append("obj.projectId, ");
        queryBuilder.append("obj.title, ");
        queryBuilder.append("obj.costCenterNumber, ");
        queryBuilder.append("obj.projectTypeDesc, ");
        queryBuilder.append("obj.status, ");
        queryBuilder.append("obj.region, ");
        queryBuilder.append("obj.country, ");
        queryBuilder.append("obj.acronym, ");
        queryBuilder.append("obj.projectStartDate, ");
        queryBuilder.append("obj.projectEndDate, ");
        queryBuilder.append("obj.partnerName, ");
        queryBuilder.append("obj.budgetOverride, ");
        queryBuilder.append("obj.statusOverride, ");
        queryBuilder.append("obj.totalFunds, ");
        queryBuilder.append("obj.remainingFunds, ");
        queryBuilder.append("obj.remainingFunds");
        if (excludePaymentRequest != null && excludePaymentRequest > 0) {
            parameters.add(excludePaymentRequest);
            queryBuilder.append(" + ISNULL((SELECT pr.totalAmount FROM PaymentRequestView pr WHERE pr.paymentRequestId = ?"
                + " AND pr.projectId = obj.id), 0)");
        }
        queryBuilder.append(" FROM ");
        queryBuilder.append("ProjectPaymentView obj ");
        queryBuilder.append(" WHERE ");
        queryBuilder.append("UPPER(obj.status) = ?");
        queryBuilder.append(" OR ");
        queryBuilder.append("(");
        queryBuilder.append(" UPPER(obj.status) = ?");
        queryBuilder.append(" AND ");
        queryBuilder.append(" UPPER(obj.projectTypeDesc) = ?");
        queryBuilder.append(" AND ");
        queryBuilder.append(" obj.projectEndDate IS NOT NULL");
        queryBuilder.append(")");
        queryBuilder.append(" OR ");
        queryBuilder.append(" obj.budgetOverride = ?");
        queryBuilder.append(" OR ");
        queryBuilder.append(" obj.statusOverride = ?");
        
        parameters.add(getText("statusname.status.project.active").toUpperCase());
        parameters.add(getText("statusname.status.project.over").toUpperCase());
        parameters.add(getText("projecttype.partner").toUpperCase());
        parameters.add(Boolean.TRUE);
        parameters.add(Boolean.TRUE);

        if (excludePaymentRequest != null && excludePaymentRequest > 0) {
            parameters.add(excludePaymentRequest);
            queryBuilder.append(" OR ");
            queryBuilder.append(" EXISTS (SELECT 1 FROM PaymentRequestView pr WHERE pr.paymentRequestId = ?"
                + " AND pr.projectId = obj.id)");
        }
        queryBuilder.append(" ORDER BY obj.projectId, obj.projectTypeDesc ASC ");

        Query query = dao.createQuery(queryBuilder.toString());
        for (int i = 0; i < parameters.size(); i++) {
            query.setParameter(i + 1, parameters.get(i));
        }
        List<ProjectPaymentView> prViews = convert(query.getResultList());
        return filterProjectPayments(prViews);
    }
    
    @SuppressWarnings("rawtypes")
    private List<ProjectPaymentView> convert(List list) {
        List<ProjectPaymentView> results = new ArrayList<ProjectPaymentView>();
        for (Object obj : list) {
            Object[] arr = (Object[]) obj;
            ProjectPaymentView pr = new ProjectPaymentView();
            pr.setId((Integer) arr[0]);
            pr.setProjectId((String) arr[1]);
            pr.setTitle((String) arr[2]);
            pr.setCostCenterNumber((String) arr[3]);
            pr.setProjectTypeDesc((String) arr[4]);
            pr.setStatus((String) arr[5]);
            pr.setRegion((String) arr[6]);
            pr.setCountry((String) arr[7]);
            pr.setAcronym((String) arr[8]);
            pr.setProjectStartDate((Date) arr[9]);
            pr.setProjectEndDate((Date) arr[10]);
            pr.setPartnerName((String) arr[11]);
            pr.setBudgetOverride((Boolean) arr[12]);
            pr.setStatusOverride((Boolean) arr[13]);
            pr.setTotalFunds((BigDecimal) arr[14]);
            pr.setRemainingFunds((BigDecimal) arr[15]);
            pr.setRemainingFundsPRExcluded((BigDecimal) arr[16]);
            results.add(pr);
        }
        return results;
    }

    private List<ProjectPaymentView> filterProjectPayments(List<ProjectPaymentView> prs) {
        List<ProjectPaymentView> results = new ArrayList<ProjectPaymentView>();
        for (ProjectPaymentView pr : prs) {
            if (!NumberUtil.INSTANCE.isEqual(pr.getRemainingFunds(), pr.getRemainingFundsPRExcluded())
                || paymentHelper.canAcceptPayment(pr)) {
                results.add(pr);
            }
        }
        return results;
    }

	/**
	 * @param id
	 * @return
	 */
	@Transactional(readOnly = true)
	public CipeprojectBean getCipeproject(Integer id) {
		Assert.notNull(id);
		CipeprojectBean result = null;
		Cipeproject dbCipeproject = findById(id);
		if (dbCipeproject != null) {
			result = new CipeprojectBean();
			result.setNedIdentifier(dbCipeproject.getNedIdentifier());
			result.setDonorName(dbCipeproject.getDonoraward().getDonor().getDonorName());
			result.setAcronym(dbCipeproject.getAcronym());
			result.setTitle(dbCipeproject.getProjectTitle());
			result.setCostCenterNumber(dbCipeproject.getCostCenterNumber());
			result.setCountry(dbCipeproject.getCountry().getId().toString());
			result.setRegion(dbCipeproject.getRegion().getId().toString());

			if(dbCipeproject.getProgramAssistant()!=null)
			result.setProgramAssistant(dbCipeproject.getProgramAssistant().getId().toString());
			if(dbCipeproject.getProgramOfficer()!=null)
			result.setProgramOfficer(dbCipeproject.getProgramOfficer().getId().toString());
			if(dbCipeproject.getSeniorProgramOfficer()!=null)
			result.setSeniorProgramOfficer(dbCipeproject.getSeniorProgramOfficer().getId().toString());
			
			Map<String, String> donoraward = null;
			if (null != dbCipeproject.getDonoraward()) {
				donoraward = new HashMap<String, String>();
				donoraward.put(dbCipeproject.getDonoraward().getId().toString(), dbCipeproject.getDonoraward().getDonorAwardNumber());
			}
			result.setDonoraward(donoraward);
			String pattern = DateUtil2.DEFAULT_PATTERN;
			result.setFormattedStartDate(DateUtil2.INSTANCE.formatDate(pattern, dbCipeproject.getProjectStartDate()));
			result.setFormattedEndDate(DateUtil2.INSTANCE.formatDate(pattern, dbCipeproject.getProjectEndDate()));
		}
		return result;
	}

	/**
	 * @return
	 */
	public List<Cipeproject> getPartnerParentList() {
		List<Cipeproject> cipeprojectList = new ArrayList<Cipeproject>();
		try {
			Cipeprojecttype type = this.cipeprojecttypeService.findByDisplayName(Constants.PartnerGrant);
			Cipeprojectstatus status = this.cipeprojectstatusService.findByDisplayName(Constants.Active);
			String query = "from Cipeproject"
					 + " order by costCenterNumber asc";
			
			cipeprojectList = this.dao.getResultList(query);
		} catch (Exception exp) {
			logger.error(exp.getMessage(), exp);
		}
		return cipeprojectList;
	}

	/**
	 * @return
	 */
	public Map<Integer, String> getThemeList() {
		Map<Integer, String> themes = new HashMap<Integer, String>();
		/*
		 * themes.put(1, "abc"); themes.put(2, "cde");
		 */// TODO Auto-generated method stub
		return themes;
	}

	@Transactional(propagation = Propagation.REQUIRED, readOnly = false, rollbackFor = { CipeprojectException.class })
	public Cipeproject saveOrUpdatePartnerGrantCipeProject(Cipeproject cipeproject, Integer userId, boolean validateUniqueProject) throws CipeprojectException {
		Cipeproject savedCipeproject = saveCipeproject(cipeproject, userId, validateUniqueProject);

		if (cipeproject.getId() == null || cipeproject.getId() == 0) {
			Audits audit = new Audits();

			User user = UserUtil.INSTANCE.getCurrentUser();

			audit.setCipeproject(savedCipeproject);
			audit.setCreatedBy(user);
			audit.setUpdatedBy(user);
			audit.setCreationDate(DateUtil.getCurrentDate());
			audit.setUpdationDate(DateUtil.getCurrentDate());

			auditsService.persist(audit);
		} /*
		 * else { // TODO: probably update setUpdatedBy() & setUpdationDate()
		 * methods }
		 */
		// logger.debug("projecttype  " +
		// cipeproject.getCipeprojecttype().getId());
		return savedCipeproject;
	}

	public Cipeproject saveCipeproject(Cipeproject cipeproject, Integer userId, boolean validateUniqueProject) throws CipeprojectException {
		if (cipeproject != null) {
			User user = UserUtil.INSTANCE.getCurrentUser();
			Cipeproject dbCipeproject = findById(cipeproject.getId());
			if (dbCipeproject == null) {
				dbCipeproject = new Cipeproject();
				dbCipeproject.setCreatedBy(user);
				dbCipeproject.setCreationDate(DateUtil.getCurrentDate());
			} else {
				saveModification(dbCipeproject, cipeproject, user);
			}

			if (validateUniqueProject && cipeproject.getProjectID() != null && cipeproject.getId() == null) {
				Long total = this.countByProjectID(cipeproject.getProjectID());
				if (total > 0) {
					throw new CipeprojectException(CipeprojectException.DUPLICATE_PROJECT_ID_FOUND, "Duplicate Project ID found. Please enter unique Project ID.");
				}
			}
			if (null != cipeproject.getProjectID()) {
				dbCipeproject.setProjectID(cipeproject.getProjectID());
			}
			if (cipeproject.getMasterProject() != null && cipeproject.getMasterProject().getId() != null && cipeproject.getMasterProject().getId() > 0) {
				dbCipeproject.setMasterProject(cipeproject.getMasterProject());
			} else {
				dbCipeproject.setMasterProject(null);
			}
			dbCipeproject.setFiscalYear(cipeproject.getFiscalYear());
			dbCipeproject.setMatchingFunds(cipeproject.getMatchingFunds());
			dbCipeproject.setPartnerGrantMatchAmount(cipeproject.getPartnerGrantMatchAmount());
			dbCipeproject.setPartnerGrantFunds(cipeproject.getPartnerGrantFunds());
			dbCipeproject.setPartnerGrantStartDate(cipeproject.getPartnerGrantStartDate());
			dbCipeproject.setPartnerGrantEndDate(cipeproject.getPartnerGrantEndDate());
			dbCipeproject.setAcronym(cipeproject.getAcronym());
			dbCipeproject.setProjectimpact(cipeproject.getProjectimpact());
			if(cipeproject.getReceivedingrantsforapproval()!=null && !cipeproject.getReceivedingrantsforapproval().equals(""))
                dbCipeproject.setReceivedingrantsforapproval(cipeproject.getReceivedingrantsforapproval());
			
			if(cipeproject.getReceiptOfSignedAgreement()!=null && !cipeproject.getReceiptOfSignedAgreement().equals(""))
               dbCipeproject.setReceiptOfSignedAgreement(cipeproject.getReceiptOfSignedAgreement());
			
			if(cipeproject.getReceiptOfSignatureAuthorization()!=null && !cipeproject.getReceiptOfSignatureAuthorization().equals(""))
               dbCipeproject.setReceiptOfSignatureAuthorization(cipeproject.getReceiptOfSignatureAuthorization());
			
			if(cipeproject.getReceiptOfBankInformation()!=null && !cipeproject.getReceiptOfBankInformation().equals(""))
               dbCipeproject.setReceiptOfBankInformation(cipeproject.getReceiptOfBankInformation());

			if (cipeproject.getPartner() != null && cipeproject.getPartner().getId() != null && cipeproject.getPartner().getId() > 0) {
				dbCipeproject.setPartner(cipeproject.getPartner());
			} else {
				dbCipeproject.setPartner(null);
			}

			if (cipeproject.getParentProject() != null && cipeproject.getParentProject().getId() != null && cipeproject.getParentProject().getId() > 0
					&& cipeproject.getFormName() == null) {
				dbCipeproject.setParentProject(cipeproject.getParentProject());
			}
			// else {
			// dbCipeproject.setParentProject(null);
			// }

			dbCipeproject.setProjectTitle(cipeproject.getProjectTitle());
			if (cipeproject.getFormName() == null) {
				dbCipeproject.setApprovedFileLocation(cipeproject.getApprovedFileLocation());
				dbCipeproject.setNedIdentifier(cipeproject.getNedIdentifier());
				dbCipeproject.setFileLocation(cipeproject.getFileLocation());
				dbCipeproject.setCostCenterNumber(cipeproject.getCostCenterNumber());
				dbCipeproject.setAlphaidentifier(cipeproject.getAlphaidentifier());
			}

			if (cipeproject.getDonoraward() != null && cipeproject.getDonoraward().getId() != null && cipeproject.getDonoraward().getId() > 0) {
				dbCipeproject.setDonoraward(cipeproject.getDonoraward());
			} else {
				dbCipeproject.setDonoraward(null);
			}

			dbCipeproject.setProjectStartDate(cipeproject.getProjectStartDate());
			dbCipeproject.setProjectEndDate(cipeproject.getProjectEndDate());
			Cipeprojecttype cipeprojecttype = cipeproject.getCipeprojecttype();
			if (null != cipeprojecttype) {
				dbCipeproject.setCipeprojecttype(cipeprojecttype);
			} else {
				Cipeprojecttype type = this.cipeprojecttypeService.findByDisplayName(Constants.PartnerGrant);
				dbCipeproject.setCipeprojecttype(type);
			}
			Cipeprojectstatus cipeprojectstatus = cipeproject.getCipeprojectstatus();
			if (null != cipeproject) {
				dbCipeproject.setCipeprojectstatus(cipeprojectstatus);
			}
			Region region = cipeproject.getRegion();
			if (null != region) {
				dbCipeproject.setRegion(region);
			}
			Country country = cipeproject.getCountry();
			if (null != country) {
				dbCipeproject.setCountry(country);
			}
			User seniorProgramOfficer = cipeproject.getSeniorProgramOfficer();
			if (null != seniorProgramOfficer) {
				dbCipeproject.setSeniorProgramOfficer(seniorProgramOfficer);
			}
			User programOfficer = cipeproject.getProgramOfficer();
			if (null != programOfficer) {
				dbCipeproject.setProgramOfficer(programOfficer);
			}
			User programAssistant = cipeproject.getProgramAssistant();
			if (null != programAssistant && programAssistant.getId() != null && programAssistant.getId() > 0) {
				dbCipeproject.setProgramAssistant(programAssistant);
			} else {
				dbCipeproject.setProgramAssistant(null);
			}
			Organizationtype organzatOrganizationtype = cipeproject.getOrganizationtype();
			if (null != organzatOrganizationtype) {
				dbCipeproject.setOrganizationtype(organzatOrganizationtype);
			}
			dbCipeproject.setUpdatedBy(user);
			dbCipeproject.setManagementFunds(cipeproject.getManagementFunds());
			dbCipeproject.setRiskAssessmentScore(cipeproject.getRiskAssessmentScore());
			dbCipeproject.setMemo(cipeproject.getMemo());
			dbCipeproject.setAccountingQuestionnaireReceived(cipeproject.getAccountingQuestionnaireReceived());
			dbCipeproject.setTerroristCheckPerformed(cipeproject.getTerroristCheckPerformed());
			dbCipeproject.setBlockTimeSheet(cipeproject.getBlockTimeSheet());
			dbCipeproject.setOverride(cipeproject.getOverride());
			dbCipeproject.setOverrideAmount(cipeproject.getOverrideAmount());
			dbCipeproject.setOverridePeriodStartDate(cipeproject.getOverridePeriodStartDate());
			dbCipeproject.setOverridePeriodEndDate(cipeproject.getOverridePeriodEndDate());
			dbCipeproject.setStatusoverride(cipeproject.getStatusoverride());
			// theme info
			dbCipeproject.setThemeaccesstoinfo(cipeproject.getThemeaccesstoinfo());
			dbCipeproject.setThemebusassocdev(cipeproject.getThemebusassocdev());
			dbCipeproject.setThemecorporategovernance(cipeproject.getThemecorporategovernance());
			dbCipeproject.setThemecorruption(cipeproject.getThemecorruption());
			dbCipeproject.setThemedemocraticgovernance(cipeproject.getThemedemocraticgovernance());
			dbCipeproject.setThemeinformalsectorproperty(cipeproject.getThemeinformalsectorproperty());
			dbCipeproject.setThemelegalregreform(cipeproject.getThemelegalregreform());
			dbCipeproject.setThemewomenyouth(cipeproject.getThemewomenyouth());
			// line of work
			dbCipeproject.setLowcipeadvocacy(cipeproject.getLowcipeadvocacy());
			dbCipeproject.setLowpartnership(cipeproject.getLowpartnership());
			dbCipeproject.setLowproorgdevelopment(cipeproject.getLowproorgdevelopment());
			dbCipeproject.setUpdationDate(DateUtil.getCurrentDate());
			dbCipeproject.setFileLocation(cipeproject.getFileLocation());
			if (dbCipeproject.getId() == null || dbCipeproject.getId() == 0) {
				persist(dbCipeproject); // for creating the entity
			} else {
				dbCipeproject = merge(dbCipeproject); // for merging (merge does
														// not feed
				// automatically the pk if it is null)
			}
			updatePrevValues(dbCipeproject);
			return dbCipeproject;
		}
		return null;
	}

	public Cipeproject saveKnowledgeManagement(Cipeproject cipeproject, Integer userId, boolean validateUniqueProject) throws CipeprojectException {
		if (cipeproject != null) {
			User user = UserUtil.INSTANCE.getCurrentUser();
			Cipeproject dbCipeproject = findById(cipeproject.getId());
			if (dbCipeproject == null) {
				dbCipeproject = new Cipeproject();
				dbCipeproject.setCreatedBy(user);
				dbCipeproject.setCreationDate(DateUtil.getCurrentDate());
			} else {
				// saveModification(dbCipeproject, cipeproject, user);
			}
			dbCipeproject.setProjectimpact(cipeproject.getProjectimpact());
			Organizationtype organzatOrganizationtype = cipeproject.getOrganizationtype();
			if (null != organzatOrganizationtype && null != organzatOrganizationtype.getId()) {
				dbCipeproject.setOrganizationtype(organzatOrganizationtype);
			} else {
				dbCipeproject.setOrganizationtype(null);
			}
			// theme info
			dbCipeproject.setThemeaccesstoinfo(cipeproject.getThemeaccesstoinfo());
			dbCipeproject.setThemebusassocdev(cipeproject.getThemebusassocdev());
			dbCipeproject.setThemecorporategovernance(cipeproject.getThemecorporategovernance());
			dbCipeproject.setThemecorruption(cipeproject.getThemecorruption());
			dbCipeproject.setThemedemocraticgovernance(cipeproject.getThemedemocraticgovernance());
			dbCipeproject.setThemeinformalsectorproperty(cipeproject.getThemeinformalsectorproperty());
			dbCipeproject.setThemelegalregreform(cipeproject.getThemelegalregreform());
			dbCipeproject.setThemewomenyouth(cipeproject.getThemewomenyouth());
			// line of work
			dbCipeproject.setLowcipeadvocacy(cipeproject.getLowcipeadvocacy());
			dbCipeproject.setLowpartnership(cipeproject.getLowpartnership());
			dbCipeproject.setLowproorgdevelopment(cipeproject.getLowproorgdevelopment());
			// Set project keywords
			if (null != cipeproject.getSelectedKeywords()) {
				// List<Keyword> projectkeywords = dbCipeproject.getKeywords();
				List<Keyword> projectkeywords = new ArrayList<Keyword>();
				Set<Keyword> keywordSet = new HashSet<Keyword>(projectkeywords);
				for (String keyword : cipeproject.getSelectedKeywords()) {
					Keyword obj = this.keywordService.findById(Integer.valueOf(keyword));
					keywordSet.add(obj);
				}
				List<Keyword> keywordList = new ArrayList<Keyword>(keywordSet);
				dbCipeproject.setKeywords(keywordList);
			}
			dbCipeproject.setUpdatedBy(user);
			dbCipeproject.setUpdationDate(DateUtil.getCurrentDate());
			if (dbCipeproject.getId() == null || dbCipeproject.getId() == 0) {
				persist(dbCipeproject); // for creating the entity
			} else {
				merge(dbCipeproject); // for merging (merge does not feed
				// automatically the pk if it is null)
			}
			return dbCipeproject;
		}
		return null;
	}

	private void saveModification(Cipeproject dbObject, Cipeproject bean, User user) {
		List<Cipeprojectmodification> modifications = dbObject.getCipeprojectmodifications();
		if (modifications == null) {
			modifications = new ArrayList<Cipeprojectmodification>();
			dbObject.setCipeprojectmodifications(modifications);
		}

		boolean isPartner = dbObject.getCipeprojecttype() != null ? getText("projecttype.partner").equalsIgnoreCase(dbObject.getCipeprojecttype().getDisplayName()) : false;

		if (!DateUtil2.INSTANCE.isSameDay(dbObject.getProjectStartDate(), bean.getProjectStartDate())) {
			Cipeprojectmodification modification = createModication(dbObject, bean, user);
			Modificationtype type = modificationTypeService.getModificationTypeByCodeOrName(getText("code.modificationType.changed.startDate"));
			modification.setModificationType(type);
			modification.setModifiedFrom(DateUtil2.INSTANCE.formatDate(dbObject.getProjectStartDate()));
			modification.setModifiedTo(DateUtil2.INSTANCE.formatDate(bean.getProjectStartDate()));
			modifications.add(modification);
		}

		if (!DateUtil2.INSTANCE.isSameDay(dbObject.getProjectEndDate(), bean.getProjectEndDate())) {
			Cipeprojectmodification modification = createModication(dbObject, bean, user);
			Modificationtype modificationType = modificationTypeService.getModificationTypeByCodeOrName(getText("code.modificationType.changed.endDate"));
			modification.setModificationType(modificationType);
			modification.setModifiedFrom(DateUtil2.INSTANCE.formatDate(dbObject.getProjectEndDate()));
			modification.setModifiedTo(DateUtil2.INSTANCE.formatDate(bean.getProjectEndDate()));
			modifications.add(modification);
			if (dbObject.getAudits() != null ? dbObject.getAudits().getFinalaudit() : false) {
				Calendar cal = Calendar.getInstance();
				cal.setTime(bean.getProjectEndDate());
				cal.add(Calendar.DATE, -30);
				dbObject.getAudits().setFaselectionmemodue(cal.getTime());
				cal.setTime(bean.getProjectEndDate());
				cal.add(Calendar.MONTH, 2);
				dbObject.getAudits().setFadue(cal.getTime());
			}

			if (dbObject.getAudits() != null ? dbObject.getAudits().getInterimaudit() : false) {
				Calendar cal = Calendar.getInstance();
				cal.setTime(bean.getProjectStartDate());
				cal.add(Calendar.MONTH, 8);
				dbObject.getAudits().setIadue(cal.getTime());
				cal.setTime(bean.getProjectStartDate());
				cal.add(Calendar.MONTH, 3);
//				Interim Selection Memo Due Date
				dbObject.getAudits().setIaselectionmemodue(cal.getTime());
			}
		}

		if (bean.getFormName() == null && !NumberUtil.INSTANCE.isEqual(dbObject.getManagementFunds(), bean.getManagementFunds())) {
			Cipeprojectmodification modification = createModication(dbObject, bean, user);
			Modificationtype modificationType = null;
			if (dbObject.getManagementFunds().compareTo(bean.getManagementFunds()) < 0) {
				modificationType = modificationTypeService.getModificationTypeByCodeOrName(getText("code.modificationType.changed.managementFunds.increased"));
			} else {
				modificationType = modificationTypeService.getModificationTypeByCodeOrName(getText("code.modificationType.changed.managementFunds.reduced"));
			}
			modification.setModificationType(modificationType);
			modification.setModifiedFrom(dbObject.getManagementFunds().toPlainString());
			modification.setModifiedTo(bean.getManagementFunds().toPlainString());
			modifications.add(modification);
		}

		if (bean.getPartnerGrantFunds()!=null && isPartner && !NumberUtil.INSTANCE.isEqual(dbObject.getPartnerGrantFunds(), bean.getPartnerGrantFunds())) {
			Cipeprojectmodification modification = createModication(dbObject, bean, user);
			Modificationtype modificationType = null;
			if (dbObject.getPartnerGrantFunds().compareTo(bean.getPartnerGrantFunds()) < 0) {
				modificationType = modificationTypeService.getModificationTypeByCodeOrName(getText("code.modificationType.changed.partnerGrantFunds.increased"));
			} else {
				modificationType = modificationTypeService.getModificationTypeByCodeOrName(getText("code.modificationType.changed.partnerGrantFunds.reduced"));
			}
			modification.setModificationType(modificationType);
			modification.setModifiedFrom(dbObject.getPartnerGrantFunds().toPlainString());
			modification.setModifiedTo(bean.getPartnerGrantFunds().toPlainString());
			modifications.add(modification);
		}

		Integer prevStatusId = dbObject.getCipeprojectstatus() != null ? dbObject.getCipeprojectstatus().getId() : null;
		Integer currStatusId = bean.getCipeprojectstatus() != null ? bean.getCipeprojectstatus().getId() : null;

		if (!NumberUtil.INSTANCE.isEqual(prevStatusId, currStatusId)) {
			Cipeprojectmodification modification = createModication(dbObject, bean, user);
			Modificationtype modificationType = modificationTypeService.getModificationTypeByCodeOrName(getText("code.modificationType.changed.status"));
			modification.setModificationType(modificationType);
			modification.setModifiedFrom(dbObject.getCipeprojectstatus().getDisplayName());
			if (currStatusId != null && currStatusId > 0) {
				Cipeprojectstatus newStatus = cipeprojectstatusService.findById(currStatusId);
				modification.setModifiedTo(newStatus.getDisplayName());
			} else {
				modification.setModifiedTo("");
			}
			modifications.add(modification);
		}
	}

	private Cipeprojectmodification createModication(Cipeproject dbObject, Cipeproject bean, User user) {
		Cipeprojectmodification modification = new Cipeprojectmodification();
		Date currentDate = new Date();
		modification.setCreatedBy(user);
		modification.setCreationDate(currentDate);
		modification.setModifiedBy(user);
		modification.setModifiedDate(currentDate);
		modification.setCipeproject(dbObject);
		modification.setAttachment(bean.getModificationDocumentLocation());
		return modification;
	}

	@Transactional(readOnly = true)
	public Cipeproject getCipeProject(Integer projectId, List<String> columns) throws QueryException {
		StringBuilder query = new StringBuilder("SELECT ");
		if (columns != null && columns.size() > 0) {
			int i = 0;
			for (; i < columns.size() - 1; i++) {
				query.append("obj." + columns.get(i) + ", ");
			}
			query.append("obj." + columns.get(i));
		} else {
			query.append("obj");
		}
		query.append(" FROM ");
		query.append(Cipeproject.class.getName());
		query.append(" obj ");
		query.append(" WHERE obj.id = ?1");
		List<Cipeproject> list = dao.getResultList(query.toString(), Cipeproject.class, columns.toArray(new String[0]), new Object[] { projectId });
		return list != null && list.size() > 0 ? list.get(0) : null;
	}

	public Cipeproject findById(Serializable id) {
		Cipeproject project = super.findById(id);
		return project != null ? updatePrevValues(project) : project;
	}

	private Cipeproject updatePrevValues(Cipeproject project) {
		if (project.getProjectStartDate() != null) {
			project.setPrevStartDate(DateUtil2.INSTANCE.formatDate(project.getProjectStartDate()));
		}

		if (project.getProjectEndDate() != null) {
			project.setPrevEndDate(DateUtil2.INSTANCE.formatDate(project.getProjectEndDate()));
		}

		if (project.getCipeprojectstatus() != null) {
			project.setPrevStatusId(project.getCipeprojectstatus().getId());
		}

		if (project.getManagementFunds() != null) {
			project.setPrevManagementFunds(project.getManagementFunds());
		}

		if (project.getPartnerGrantFunds() != null) {
			project.setPrevPartnerGrantFunds(project.getPartnerGrantFunds());
		}
		return project;
	}
	
	/**
	 * get the start and end date of parent project for a single project
	 * @param id
	 * @return CipeProject object
	 */
	public Cipeproject getParentProjectStartandEndDates(Integer id) {
		Cipeproject cipeproject = null;
		try {
			 String query = "FROM Cipeproject c where c.id=(select parentProject.id  FROM Cipeproject c where c.id ='"+id+"')";
				 cipeproject = this.dao.getSingleResult(query);
		} catch (Exception exp) {
			logger.error(exp.getMessage(), exp);
		}
		return cipeproject;
	}
	
	 public Cipeproject getCipeProjectByPaymentReqId(int paymentReqId)
	 {
	    	Cipeproject cipeProject;
	    	String query="SELECT lineitem.cipeproject FROM Paymentrequestlineitem lineitem where lineitem.paymentrequest.id ="+paymentReqId;
	        List<Cipeproject> cipeProjectList=this.dao.getResultList(query);	      
	    	  return (cipeProjectList.size()>0?cipeProjectList.get(0):null);
	      
	    	
	  }
	
}
